#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import requests
from bs4 import BeautifulSoup
import pymysql
import send_to_mail
import time
import socket
import logging

logging.basicConfig(level=logging.INFO,\
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',\
                    datefmt='%a, %d %b %Y %H:%M:%S',\
                    filename='zxhr.log',\
                    filemode='w')

url_zxhr = 'http://www.cxhr.com/search/jobList.jsp?curpage=%d&&keywords=外贸'

header = {
    'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36'
}

def next_page(url, id):
    try:
        res = requests.get(url % id, headers=header)
        res.encoding = 'utf-8'
    except socket.gaierror as e:
        logging.warning('[error] %s' % e)
        return None
    except ConnectionError as e:
        logging.warning('[error] %s' % e)
        return None
    return res.text


def parser_html(html):
    soup = BeautifulSoup(html, 'lxml')

    data = []
    for i in soup.tbody.descendants:
        if i.name == 'td':
            data.append(i.text)

    return data


def clear_data(data):
    tmp = []
    for i in data:
        if i:
            tmp.append(i)

    new_data = []
    index = 0
    i = 0
    while i < len(tmp):
        new_data.append(tmp[i])
        i += 1
        index += 1

        if index > 5:
            index = 0
            i += 3

    clr_data = []
    for i in new_data:
        tmp = filter(str.isalnum, i)
        tmp_str = ''
        for j in tmp:
            tmp_str += j

        clr_data.append(tmp_str)

    return clr_data


def fold_data(data):
    out = []
    i = 0
    while i < len(data):
        _in = []
        for j in range(6):
            _in.append(data[i])
            i += 1

        out.append(_in)
    return out


class mariadb:

    def __init__(self):
        self.db = pymysql.connect('localhost', 'spider', 'spider_password', 'spiderdb', charset='utf8')
        self.cursor = self.db.cursor()
        self.str_time = time.strftime("%Y%m%d")
        self.cur_time = time.strftime("%H:%M")


    def check_repeat(self, list):
        sql = "SELECT id FROM zxhr WHERE title = %s AND number = %s AND company = %s AND education = %s AND place = %s AND date = %s;"
        try:
            for i in list:
                if i is None:
                    i = '0'
            # print(list)
            self.cursor.execute(sql % (list[0], list[1], list[2], list[3], list[4], list[5]))
            result = self.cursor.fetchall()
        except pymysql.err.InternalError as e:
            logging.warning('[error]  %s' % e)
            result = None

        if result:
            for i in result:
                id = i[0]
                sql_update = "UPDATE zxhr SET is_new = 0 where id = %d;"
                self.cursor.execute(sql_update % id)
                self.db.commit()

            return True
        else:
            return False


    def query(self):
        table = []
        sql = "SELECT id, title, number, company, education, place, date, is_new FROM zxhr WHERE date = %s ORDER BY is_new DESC;"
        strtime = time.strftime("%Y%m%d")

        try:
            self.cursor.execute(sql % strtime)
            result = self.cursor.fetchall()
            for i in result:
                table.append(list(i))
        except:
            logging.warning('[error] query data failed')

        return table


    def update_company(self):
        query_sql = "SELECT * FROM zxhr WHERE date = %s;"
        update_sql = "UPDATE zxhr SET is_new = 0 where id = %d;"

        try:
            tmp = "'" + self.str_time + "'"
            self.cursor.execute(query_sql % tmp)
            result = self.cursor.fetchall()
            for i in result:
                if i[-1] != self.cur_time:
                    try:
                        self.cursor.execute(update_sql % i[0])
                        self.db.commit()
                    except:
                        self.db.rollback()
        except:
            pass

    def exist_company(self, data):
        sql = "SELECT company FROM zxhr where company = '%s' and date = %s;"
        strtime = time.strftime("%Y%m%d")

        company = []
        try:
            self.cursor.execute(sql % (data, strtime))
            result = self.cursor.fetchall()
            for i in result:
                company.append(i)

        except:
            pass

        return len(company) != 0

    def insert(self, data):
        for i in data:
            index = 2

            tmp_str = "'" + i[0] + "'"
            i[0] = tmp_str
            while index < 6:
                __tmp = "'" + i[index] + "'"
                i[index] = __tmp
                index += 1

            if self.check_repeat(i):
                continue

            if self.exist_company(i[2]):
                sql = "INSERT INTO zxhr VALUES (NULL, %s, %s, %s, %s, %s, %s, 0, %s);"
            else:
                sql = "INSERT INTO zxhr VALUES (NULL, %s, %s, %s, %s, %s, %s, 1, %s);"

            try:
                # 执行sql语句
                tmp = "'" + self.cur_time +"'"
                sqlcommand = sql%(i[0],i[1],i[2],i[3],i[4],i[5],tmp)
                self.cursor.execute(sqlcommand)
                # 执行sql语句
                self.db.commit()
            except:
                # 发生错误时回滚
                logging.warning('insert to database error...')
                self.db.rollback()


    def __del__(self):
        self.db.close()


if __name__ == '__main__':
    db = mariadb()
    i = 1
    while True:
        html = next_page(url_zxhr, i)
        logging.info('get html > %d' % i)
        if not html:
            logging.warning('no page')
            break

        tmp_data = parser_html(html)

        form_data = tmp_data[11:-11]
        data = clear_data(form_data)

        strtime = time.strftime("%Y%m%d")
        fold = fold_data(data)

        if fold[0][-1] != strtime:
            break

        db.insert(fold)
        i += 1
        time.sleep(2)

    db.update_company()
    data = db.query()

    flag = False
    for i in data:
        if i[-1] == 1:
            flag = True

    table = send_to_mail.make_table(['职位名称', '招聘人数', '公司名称', '学历', '地点', '更新时间', '新条目'], data)
    if flag:
        send_to_mail.send_email('haokabu@sina.com', table, '慈溪人才网')
    else:
        logging.info('data update success..but not sent email, program exit -> 0')
